package Dao;

import Bean.Ticket;
import Utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 张
 * Date: 2025-02-28
 * Time: 18:28
 */
public class TicketDao {
    private Connection connection = null;
    private PreparedStatement statement = null;
    private ResultSet resultSet = null;

    //1.用户购买机票
    public boolean insert(Ticket ticket) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "insert into ticket values(?,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,ticket.getTicketId());
            statement.setString(2,ticket.getUserId());
            statement.setString(3,ticket.getFlightId());
            statement.setString(4,ticket.getSeatNumber());
            statement.setDouble(5,ticket.getPrice());
            statement.setString(6,ticket.getStatus().name());
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }



    //3.删除机票
    public boolean delete (String ticketId) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装sql语句
            String sql = "delete from ticket where ticket_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,ticketId);
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }


    public int size(String userId) {
        int size = 0;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS ticket_count FROM ticket where status IN ('已预订', '已支付')";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("ticket_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }

    //2.查询对应用户的所有机票 -- 只返回状态为已预定和已支付的数据
    public List selectByUserId(String userId, int pageNo, int pageSize) {
        List<Ticket> array = new ArrayList<>();
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "select * from ticket where user_id=? and status IN ('已预订', '已支付') LIMIT ?,? ";
            statement = connection.prepareStatement(sql);
            statement.setInt(2,(pageNo-1)*pageSize);
            statement.setInt(3,pageSize);
            statement.setString(1,userId);
            resultSet = statement.executeQuery();
            if(resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Ticket ticket = new Ticket();
                    ticket.setTicketId(resultSet.getString("ticket_id"));
                    ticket.setFlightId(resultSet.getString("flight_id"));
                    ticket.setSeatNumber(resultSet.getString("seat_number"));
                    ticket.setPrice(resultSet.getDouble("price"));
                    ticket.setStatus(Ticket.TicketStatus.valueOf(resultSet.getString("status")));
                    array.add(ticket);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }


    //查询用户航班 -- 只返回状态为已预定和已支付的数据
    public Ticket select(String userId, String flightId,String seatNumber) {
        Ticket ticket = null;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句

            String sql = "select * from ticket where user_id=? and flight_id = ? and seat_number = ? AND status IN ('已预订', '已支付')";
            statement = connection.prepareStatement(sql);
            statement.setString(1,userId);
            statement.setString(2,flightId);
            statement.setString(3,seatNumber);
            resultSet = statement.executeQuery();

            if(resultSet.next()) {
                ticket = new Ticket();
                ticket.setTicketId(resultSet.getString("ticket_id"));
                ticket.setFlightId(resultSet.getString("flight_id"));
                ticket.setSeatNumber(resultSet.getString("seat_number"));
                ticket.setPrice(resultSet.getDouble("price"));
                ticket.setStatus(Ticket.TicketStatus.valueOf(resultSet.getString("status")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return ticket;
    }

    public int update(Ticket ticket) {
        int ret = 0;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            String sql = "UPDATE ticket SET status = ? WHERE user_id = ? AND flight_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, ticket.getStatus().name());
            statement.setString(2, ticket.getUserId());
            statement.setString(3, ticket.getFlightId());
            ret = statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return ret;
    }

    public int sizeQuit(String userId) {
        int size = 0;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS ticket_count FROM ticket where status IN ('已退票', '退票中')";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("ticket_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }

    public List<Ticket> selectQuit(int pageNo, int pageSize, String userId) {
        List<Ticket> array = null;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "select * from ticket where user_id=? and status IN ('已退票', '退票中') LIMIT ?,? ";
            statement = connection.prepareStatement(sql);
            statement.setInt(2,(pageNo-1)*pageSize);
            statement.setInt(3,pageSize);
            statement.setString(1,userId);
            resultSet = statement.executeQuery();
            if(resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Ticket ticket = new Ticket();
                    ticket.setTicketId(resultSet.getString("ticket_id"));
                    ticket.setFlightId(resultSet.getString("flight_id"));
                    ticket.setSeatNumber(resultSet.getString("seat_number"));
                    ticket.setPrice(resultSet.getDouble("price"));
                    ticket.setStatus(Ticket.TicketStatus.valueOf(resultSet.getString("status")));
                    array.add(ticket);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }

    public int sizeQuits() {
        int size = 0;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS ticket_count FROM ticket where status = '退票中'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("ticket_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }

    public List selectQuits(int pageNo, int pageSize) {
        List<Ticket> array = null;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "select * from ticket where  status = '退票中' LIMIT ?,? ";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,(pageNo-1)*pageSize);
            statement.setInt(2,pageSize);
            resultSet = statement.executeQuery();
            if(resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Ticket ticket = new Ticket();
                    ticket.setUserId(resultSet.getString("user_id"));
                    ticket.setTicketId(resultSet.getString("ticket_id"));
                    ticket.setFlightId(resultSet.getString("flight_id"));
                    ticket.setSeatNumber(resultSet.getString("seat_number"));
                    array.add(ticket);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }

    public boolean Pass(Ticket ticket) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            String sql = "UPDATE ticket SET status = ? , seat_number = ? WHERE ticket_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, ticket.getStatus().name());
            statement.setString(2, ticket.getSeatNumber());
            statement.setString(3, ticket.getTicketId());
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }

    public boolean Refuse(Ticket ticket) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            String sql = "UPDATE ticket SET status = ? WHERE ticket_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, ticket.getStatus().name());
            statement.setString(2, ticket.getTicketId());
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }

    public Ticket selectByTicketId(String ticketId) {
        Ticket ticket = null;
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句

            String sql = "select * from ticket where ticket_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,ticketId);
            resultSet = statement.executeQuery();

            if(resultSet.next()) {
                ticket = new Ticket();
                ticket.setUserId((resultSet.getString("user_id")));
                ticket.setTicketId(resultSet.getString("ticket_id"));
                ticket.setFlightId(resultSet.getString("flight_id"));
                ticket.setSeatNumber(resultSet.getString("seat_number"));
                ticket.setPrice(resultSet.getDouble("price"));
                ticket.setStatus(Ticket.TicketStatus.valueOf(resultSet.getString("status")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return ticket;
    }

}
